package manager;

/**
 * @author huangjm.2012, huiwensong.2012, sheryl.tay.2012, mohameds.2012
 *
 */

import java.util.ArrayList;

import entity.Bid;
import entity.Section;
import entity.Student;

public class SectionStudentMgr {

	DatabaseMgr dbMgr;

	public SectionStudentMgr() {
		dbMgr = new DatabaseMgr();
	}

	public void addSectionEnrollmentForStudent(String courseCode,
			String sectionCode, String userId) {

		String sql = "insert into section_student (userId, courseCoded, sectionCode, bidAmount) "
				+ " values (?, ?, ?, (select bidAmount from bid where courseCode=? and sectionCode=? and userId=?)) ";

		dbMgr.openAndUpdateAndClose(sql, courseCode, sectionCode, userId,
				courseCode, sectionCode, userId);
	}

	public void deleteSectionEnrollmentForStudent(String userId,
			String courseCode, String sectionCode) {
		String sql = "delete from section_student where courseCode=? and sectionCode=? and userId=? ";

		dbMgr.openAndUpdateAndClose(sql, courseCode, sectionCode, userId);
	}

	public ArrayList<Bid> retrieveAllSuccessfulBids() {
		String sql = "select * from successful_bids_with_student_section_course "
				+ " order by courseCode, userId";

		return RetrieveBidUtil.retrieveMultipleBidsForQuery(dbMgr, sql);
	}

	public ArrayList<Section> retrieveSectionEnrollmentsForStudent(String userId) {
		String sql = "select * from successful_bids_with_student_section_course "
				+ " where userId=? order by courseCode, userId";

		return RetrieveSectiontUtil.retrieveMultipleSectionsForQuery(dbMgr,
				sql, userId);
	}

	public ArrayList<Student> retrieveEnrolledStudentsForSection(
			String courseCode, String sectionCode) {

		String sql = "select * from successful_bids_with_student_section_course "
				+ " where courseCode=? and sectionCode=? "
				+ " order by courseCode, userId ";

		return RetrieveStudentUtil.retrieveMultipleStudentsForQuery(dbMgr, sql,
				courseCode, sectionCode);
	}

	public ArrayList<Bid> retrieveSuccessfulBidsForSection(String courseCode,
			String sectionCode) {

		String sql = "select * from successful_bids_with_student_section_course "
				+ " where courseCode=? and sectionCode =? "
				+ " order by courseCode, userId";

		return RetrieveBidUtil.retrieveMultipleBidsForQuery(dbMgr, sql,
				courseCode, sectionCode);
	}

}
